<?php 
/* 
 * This file is part of TCDB. 
 * 
 * Copyright (C) 2000-2009 
 * Technology Consultant Corps 
 * Grinnell College 
 * Grinnell, IA, 50112 
 * tc@grinnell.edu 
 * 
 * TCDB is free software; you can redistribute it and/or modify 
 * it under the terms of the GNU General Public License as published by 
 * the Free Software Foundation; either version 3 of the License, or 
 * (at your option) any later version. 
 * 
 * TCDB is distributed in the hope that it will be useful, 
 * but WITHOUT ANY WARRANTY; without even the implied warranty of 
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 
 * GNU General Public License for more details. 
 * 
 * You should have received a copy of the GNU General Public License 
 * along with TCDB. If not, see <http://www.gnu.org/licenses/>. 
 * 
 
 ============================================================================= 
 
 * delete_users.php -- Allows an admin to delete any number of users in a 
 *		       single pass, saving their old information in PDFs,
 *		       zipped together for the admin. The directory is then
 *		       e-mailed to the admin for the records
 * 
 * Author: Dylan J. Sather
 * Created: 2009-07-20 
 * Last edited: 2009-07-22 
 */ 
 
$page_title = "delete_users.php"; 

require('init.php'); 
require('require_login.php'); 
require('require_admin.php'); 
require('fpdf/fpdf.php');
include('header.php'); 

?>

<div class="content_box"> <!-- CONTENT div -->

<?php

// If we don't have any usernames stored in the $_POST array, we're entering usernames to delete
if (!isset($_POST["usernames"])) {
    echo "<h3>Please enter the usernames of users you'd like to delete, separated by <span class='underline'>commas</span></h3>
    <p>If you're prompted to download a zipped directory of PDFs, the deletions were successful.<br />
       Please forward the PDFs to the head of the TC Corps for his or her records</p>
    <form method='post' action='delete_users.php'>
      <p>
        <input type='text' name='usernames' size='30' /><br />
        <input type='submit' value='Delete Users' />
      </p>
    </form>";
}

// Otherwise, we have a set of users and we have to delete them from the DB
else {

    // Take data from the usernames string and 'explode' into an array
    $usernames = explode(",", $_POST["usernames"]);

    // We also have to keep track of the users we successfully/unsuccessfully delete
    $deleted_users = array();
    $invalid_users = array();

    // Create zip archive (we're going to add PDFs to this later)
    $zip = new ZipArchive();
    $filename = "employment_history/employment_history_" . date("Y_m_d") . ".zip";

    if ($zip->open($filename, ZIPARCHIVE::CREATE) != TRUE)
	exit("Unable to create zip archive -- please try again");
    
    foreach ($usernames as $user) {

	// ALWAYS trim your whitespace around data you're using in a query (or hours_lost++)
	$user = trim($user);

	$query = sprintf("SELECT id
			  FROM users
			  WHERE username = '%s'",
		      mysqli_real_escape_string($mysqli, $user));

	$result = $mysqli->query($query, MYSQLI_STORE_RESULT);

	if ($result->num_rows != 0) {

	    list($user_id) = $result->fetch_row();

	    $result->free();
	
	    /* Before we delete a user, we want to retain her information in a PDF so that
	       can review the information after the user is gone; that way, we keep the
	       most important information while scrapping the rest */

	    // Grab info from users table
	    $query = sprintf("SELECT first_name, last_name,
				     hire_date, class,
				     rank_id, semesters
			      FROM users
			      WHERE id= '%s'",
			  mysqli_real_escape_string($mysqli, $user_id));

	    $result = $mysqli->query($query, MYSQLI_STORE_RESULT);

	    list($first_name, $last_name, $hire_date, $class, 
		 $rank_id, $semesters) = $result->fetch_row();

	    $result->free();

	    // Then grab rank names and store in array
	    $query = "SELECT id, name
		      FROM ranks";

	    $result = $mysqli->query($query, MYSQLI_STORE_RESULT);

	    $ranks = array();

	    while (list($id, $name) = $result->fetch_row()) {
		$ranks[$id] = $name;    
	    }

	    // Get the total hours worked
	    $query = sprintf("SELECT date,
				     TIME_FORMAT(start_time, '%%H'),
				     TIME_FORMAT(start_time,'%%i'),
				     TIME_FORMAT(end_time,'%%H'),
				     TIME_FORMAT(end_time,'%%i')
			      FROM hours_worked
			      WHERE user_id = '%s'
			      ORDER by date",
			  mysqli_real_escape_string($mysqli, $user_id));

	    // Since we're dealing with a large result set, we want to USE_RESULT
	    $result = $mysqli->query($query, MYSQLI_USE_RESULT);

	    // We want to keep track of total minutes
	    $total_min = 0;

	    while (list($date, $start_hr, $start_min, $end_hr, $end_min) = $result->fetch_row()) {
		$start_total = $start_min + (60 * $start_hr);

		// If it's past midnight, we have to do a special conversion
		if ($end_hr < $start_hr)
		    $end_total = $end_min + (60 * $end_hr) + (60 * 24);
		else
		    $end_total = $end_min + (60 * $end_hr);

		$total_min += $end_total - $start_total;
	    }

	    $total_hours = round($total_min / 60);

	    $result->free();

	    // Pull lab info from DB (only those that require certification)
	    $query = "SELECT id, name
		      FROM labs
		      WHERE req_cert = 1";

	    $result = $mysqli->query($query, MYSQLI_STORE_RESULT);

	    // Store lab ids and names in array
	    $labs = array();

	    while (list($id, $name) = $result->fetch_row()) {
		$labs[$id] = $name;
	    }

	    $result->free();

	    // Fetch certification info for user and store in array
	    $query = sprintf("SELECT lab_id, date
			      FROM certifications
			      WHERE user_id = '%s'",
			  mysqli_real_escape_string($mysqli, $user_id));

	    $result = $mysqli->query($query, MYSQLI_STORE_RESULT);

	    $certs = array();

	    while (list($lab_id, $date) = $result->fetch_row()) {
		$certs[$lab_id] = $date;
	    }

	    $result->free();

	    // Create a string of certifications
	    $certifications = array();

	    foreach($labs as $id => $name) {
		if (array_key_exists($id, $certs)) {
		    $certifications[] = $name;
		}
	    }

	    // We want to split the array so we have a grammatically correct string
	    $length = count($certifications);
	    $split_certs = array_slice($certifications, 0, ($length - 1));
	    $cert_string = implode(', ', $split_certs);

	    // Create a PDF with employment history
	    $pdf=new FPDF();
	    $pdf->AddPage();
	    $pdf->SetFont('Times','BU',16);
	    $pdf->Cell(20,10,"Employment history for $first_name $last_name",0,1);
	    $pdf->SetFont('Times','B',12);
	    $pdf->Cell(20,10,"$first_name was hired on $hire_date and worked $semesters semesters",0,1);
	    $pdf->Cell(20,10,"$first_name graduated in $class",0,1);
	    $pdf->Cell(20,10,"$first_name was a " . $ranks[$rank_id] . "",0,1);
	    $pdf->Cell(20,10,"$first_name worked $total_hours hours",0,1);

	    // Pull lab info from DB (only those that require certification)
	    $query = "SELECT id, name
		      FROM labs
		      WHERE req_cert = 1";

	    $result = $mysqli->query($query, MYSQLI_STORE_RESULT);

	    // Store lab ids and names in array
	    $labs = array();

	    while (list($id, $name) = $result->fetch_row()) {
		$labs[$id] = $name;
	    }

	    $result->free();

	    // Fetch certification info for user and store in array
	    $query = sprintf("SELECT lab_id, date
			      FROM certifications
			      WHERE user_id = '%s'",
			  mysqli_real_escape_string($mysqli, $user_id));

	    $result = $mysqli->query($query, MYSQLI_STORE_RESULT);
	    
	    // If the TC is certified for any special labs, write them to the PDF
	    if ($result->num_rows != 0) {

		$certs = array();

		while (list($lab_id, $date) = $result->fetch_row()) {
		    $certs[$lab_id] = $date;
		}

		$result->free();

		// Create a string of certifications
		$certifications = array();

		foreach($labs as $id => $name) {
		    if (array_key_exists($id, $certs)) {
			$certifications[] = $name;
		    }
		}

		// We want to split the array so we have a grammatically correct string
		$length = count($certifications);
		$split_certs = array_slice($certifications, 0, ($length - 1));
		$cert_string = implode(', ', $split_certs);

		// Write to PDF
		$pdf->Cell(20,10,"$first_name worked in the $cert_string and " . $certifications[($length - 1)] . "",0,1);
	    }
	    
	    // Pull existing comments from the DB
	    $query = sprintf("SELECT comment, date, submitted_by
			      FROM user_comments
			      WHERE user_id = '%s'
			      ORDER BY date",
			  mysqli_real_escape_string($mysqli, $user_id));

	    $result = $mysqli->query($query, MYSQLI_STORE_RESULT);

	    // If we have any comments, write them to the PDF
	    if ($result->num_rows != 0) {

		$pdf->SetFont('Times','BU',16);
		$pdf->Cell(20,10,"Comments",0,1);

		while (list($comment, $comment_date, $submitted_by) = $result->fetch_row()) {
		    $pdf->SetFont('Times','I',8);
		    $pdf->Cell(20,10,"Submitted on $comment_date by $submitted_by",0,1);
		    $pdf->SetFont('Times','B',12);
		    $pdf->MultiCell(0,5,$comment);
		    $pdf->Ln();
		}
	    }

	    $result->free();

	    $pdf_dir = "employment_history/";
	    $pdf_name = "History_for_$first_name" . "_$last_name.pdf";

	    // Save PDF to the 'employment_history' directory and save in zip archive
	    $pdf->Output($pdf_dir . $pdf_name, 'F');
	    $zip->addFile($pdf_dir . $pdf_name);

	    /* Finally, do the actual deletions. This involves deleting information from
	       the following tables: users, users_data, certifications, and images. We want 
	       to retain information in shifts, since other users may be taking a shift if
	       a user is fired mid-semester; the same follows for sub_requests. Finally, we
	       want to retain information in hours_worked since that's a record of the hours
	       a user worked... and that's important to keep! */

	    // We also want to keep a count to make sure we're actally deleting data
	    $delete_count = 0;

	    $query = sprintf("DELETE FROM certifications
			      WHERE user_id = '%s'",
			  mysqli_real_escape_string($mysqli, $user_id));

  	    $mysqli->query($query, MYSQLI_STORE_RESULT);

	    if ($mysqli->affected_rows != 0)
		$delete_count++;

	    $query = sprintf("DELETE FROM images
			      WHERE user_id = '%s'",
			  mysqli_real_escape_string($mysqli, $user_id));

  	    $mysqli->query($query, MYSQLI_STORE_RESULT);

	    if ($mysqli->affected_rows != 0)
		$delete_count++;

	    $query = sprintf("DELETE FROM users_data
			      WHERE user_id = '%s'",
			  mysqli_real_escape_string($mysqli, $user_id));

  	    $mysqli->query($query, MYSQLI_STORE_RESULT);

	    if ($mysqli->affected_rows != 0)
		$delete_count++;

	    $query = sprintf("DELETE FROM users
			      WHERE id = '%s'",
			  mysqli_real_escape_string($mysqli, $user_id));

  	    $mysqli->query($query, MYSQLI_STORE_RESULT);

	    if ($mysqli->affected_rows != 0)
		$delete_count++;

	    // Finally, add our user to the $deleted_users array only if all data was deleted
	    if ($delete_count >= 1)
		$deleted_users[] = "$user";
	    else
		$invalid_users[] = "$user";
	}

	/* Otherwise, we were unable to pull a user_id from the DB and should mark
	   this user as 'invalid' */
	else
	    $invalid_users[] = "$user";
    }

    // Print a confirmation that users were deleted
    echo "<h3>Accounts for the following users were deleted successfully:</h3>";

    foreach ($deleted_users as $user) {
	echo "$user<br />";
    }

    // And also list the accounts that were not successfully created
    echo "<h3>... but the script couldn't delete info on these users:</h3>";

    foreach ($invalid_users as $user) {
	echo "$user<br />";
    }

    $zip->close();
}
  
echo "</div> <!-- End CONTENT div -->";

include('footer.php'); 

?> 
